package com.example.rdcproject.ving.notes.util;

import com.example.rdcproject.ving.notes.service.ConnectionPool;
import com.example.rdcproject.ving.notes.entity.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserSearchUtil {

    private List<User> userList = new ArrayList<>();
    ;

    ConnectionPool connectionPool = null;
    Connection connection = null;
    PreparedStatement statement = null;

    public List<User> getUserList() {
        return userList;
    }

    public void setUserList(List<User> userList) {
        this.userList = userList;
    }

    public UserSearchUtil() {}


    public List searchName(String username) {
        // 从数据库中获取数据
        try {
            connectionPool = ConnectionPool.getInstance();
            connection = connectionPool.getConnection();

            String sql = "select user_nickname,user_wordCount,user_isPrivate,user_continousDays from user where user_nickname like ?";
            statement = connection.prepareStatement(sql);

            statement.setString(1, "%" + username + "%");

            ResultSet rs = statement.executeQuery();

            //遍历表获取数据
            while (rs.next()) {
                User user = new User();
                String nickname = rs.getString("user_nickname");
                Integer wordCount = rs.getInt("user_wordCount");
                Integer continousDays = rs.getInt("user_continousDays");
                boolean isPrivate = rs.getBoolean("user_isPrivate");
                user.setNickname(nickname);
                user.setWordCount(wordCount);
                user.setContinuousDays(continousDays);
                user.setPrivate(isPrivate);

                    userList.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            try {
                DBUtils.close(statement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            connectionPool.releaseConnection(connection);
        }
        return userList;
    }

    public List searchCount(int count) {
        // 从数据库中获取数据
        try {
            connectionPool = ConnectionPool.getInstance();
            connection = connectionPool.getConnection();

            String sql = "select user_nickname,user_wordCount,user_isPrivate,user_continousDays from user where user_wordCount = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, count);

            ResultSet rs = statement.executeQuery();

            //遍历表获取数据
            while (rs.next()) {
                User user = new User();
                String nickname = rs.getString("user_nickname");
                Integer wordCount = rs.getInt("user_wordCount");
                Integer continousDays = rs.getInt("user_continousDays");
                boolean isPrivate = rs.getBoolean("user_isPrivate");
                user.setNickname(nickname);
                user.setWordCount(wordCount);
                user.setContinuousDays(continousDays);
                user.setPrivate(isPrivate);

                userList.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            try {
                DBUtils.close(statement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            connectionPool.releaseConnection(connection);
        }
        return userList;
    }


    public List searchGoal(int goal) {
        // 从数据库中获取数据
        try {
            connectionPool = ConnectionPool.getInstance();
            connection = connectionPool.getConnection();

            String sql = "select user_nickname,user_wordCount,user_isPrivate,user_continousDays from user where user_dailyGoal = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, goal);

            ResultSet rs = statement.executeQuery();

            //遍历表获取数据
            while (rs.next()) {
                User user = new User();
                String nickname = rs.getString("user_nickname");
                Integer wordCount = rs.getInt("user_wordCount");
                Integer continousDays = rs.getInt("user_continousDays");
                boolean isPrivate = rs.getBoolean("user_isPrivate");
                user.setNickname(nickname);
                user.setWordCount(wordCount);
                user.setContinuousDays(continousDays);
                user.setPrivate(isPrivate);

                userList.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            try {
                DBUtils.close(statement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            connectionPool.releaseConnection(connection);
        }
        return userList;
    }

    public List searchContinous(int continous) {
        // 从数据库中获取数据
        try {
            connectionPool = ConnectionPool.getInstance();
            connection = connectionPool.getConnection();

            String sql = "select user_nickname,user_wordCount,user_isPrivate,user_continousDays from user where user_continousDay = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, continous);

            ResultSet rs = statement.executeQuery();

            //遍历表获取数据
            while (rs.next()) {
                User user = new User();
                String nickname = rs.getString("user_nickname");
                Integer wordCount = rs.getInt("user_wordCount");
                Integer continousDays = rs.getInt("user_continousDays");
                boolean isPrivate = rs.getBoolean("user_isPrivate");
                user.setNickname(nickname);
                user.setWordCount(wordCount);
                user.setContinuousDays(continousDays);
                user.setPrivate(isPrivate);

                userList.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            try {
                DBUtils.close(statement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            connectionPool.releaseConnection(connection);
        }
        return userList;
    }




    public List searchRank() {
        // 从数据库中获取数据
        try {
            connectionPool = ConnectionPool.getInstance();
            connection = connectionPool.getConnection();


            String sql = "select user_nickname,user_wordCount,user_isPrivate,user_continousDays from user ";
            statement = connection.prepareStatement(sql);

            ResultSet rs = statement.executeQuery();

            //遍历表获取数据
            while (rs.next()) {
                User user = new User();
                String nickname = rs.getString("user_nickname");
                Integer wordCount = rs.getInt("user_wordCount");
                Integer continousDays = rs.getInt("user_continousDays");
                Boolean isPrivate = rs.getBoolean("user_isPrivate");


                user.setNickname(nickname);
                user.setWordCount(wordCount);
                user.setContinuousDays(continousDays);
                user.setPrivate(isPrivate);

                userList.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            try {
                DBUtils.close(statement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            connectionPool.releaseConnection(connection);
        }
        return userList;
    }
}


